import unittest
from PIL import Image
from selenium.webdriver.support import expected_conditions as EC
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.wait import WebDriverWait
from unit__003.unit_02.Mysql import Mysql
from time import sleep
from selenium.webdriver.common.keys import Keys
from unit__003.unit_02.dama import Dama


class A3mall(unittest.TestCase):
    def setUp(self) -> None:
        print('start case')
        self.db = Mysql(host='10.59.9.14', user='root', password='yidong666', database='a3mall')
        self.driver = webdriver.Edge()
        self.driver.get('http://10.59.9.14:7003/')
        self.driver.maximize_window()

    def tearDown(self) -> None:
        print('end case')
        self.db.close()
        sleep(5)
        self.driver.quit()

    def test_01_login(self):
        sleep(2)
        # 点击请登录
        self.driver.find_element(By.XPATH, '/html/body/div[1]/div/div[2]/ul/li[1]/a').click()
        # 输入账号
        self.driver.find_element(By.NAME, 'username').send_keys('18185603008')
        # 输入密码
        self.driver.find_element(By.NAME, 'password').send_keys('123456')
        sleep(2)
        datu = self.driver.save_screenshot('datu.png')
        sleep(2)
        yzm = self.driver.find_element(By.XPATH, '//*[@id="theForm"]/div[3]/span[3]/img')
        location = yzm.location
        size = yzm.size
        print(location)
        print(size)
        left = location['x']
        top = location['y']
        regit = left + size['width']
        bottom = top + size['height']
        Image1 = Image.open('datu.png')
        Image2 = Image1.crop((left * 2, top * 2, regit * 2, bottom * 2))
        Image2.save('image2.png')
        print(Image2)
        dm = Dama('18185603008','22psuif8','970661')
        yzmdm = open('image2.png','rb').read()
        jieguo = dm.PostPic(yzmdm,1902)
        print(jieguo)
        result = jieguo['pic_str']
        print('我的打码结果是：',result)
        self.driver.find_element(By.NAME,'code').send_keys(result)
        sleep(2)
        self.driver.find_element(By.CLASS_NAME,'finish-btn').click()

    def test_02_addgwc(self):
        # 调用test_01_login
        self.test_01_login()
        sleep(5)
        # A3mall-前台
        # 断言1 会员中心
        dy1 = self.driver.find_element(By.XPATH, '/html/body/div[4]/a[2]').text
        sleep(2)
        print('断言1-会员中心:', dy1)
        self.assertEqual('会员中心', dy1)
        # 断言2 数据库断言 提取用户余额和数据库余额对比
        dy2 = self.driver.find_element(By.CLASS_NAME, 'color-black').text
        sleep(2)
        dy2s = dy2.split()
        print('断言2:', dy2s[1])
        sql1=f'select amount from mall_users where username={"18185603008"}'
        result1 = self.db.chaxun(sql1)
        print('数据库提取的用户余额:', result1[0][0])
        self.assertEqual(float(dy2s[1]), float(result1[0][0]))
        # 断言3 欢迎回来，18185607777
        dy3 = self.driver.find_element(By.XPATH, '/html/body/div[1]/div/div[2]/ul/li[1]/a').text
        sleep(2)
        print('断言3-欢迎回来，18185607777:', dy3)
        self.assertEqual('欢迎回来，18185603008', dy3)
        # 点击首页
        self.driver.find_element(By.XPATH, '/html/body/div[3]/div[2]/ul/li[1]/a').click()
        sleep(2)
        # 搜索商品-水瓶
        search = self.driver.find_element(By.CLASS_NAME,'search-input')
        search.send_keys('水瓶')
        # 键盘回车事件
        search.send_keys(Keys.ENTER)
        self.driver.implicitly_wait(2)
        # 提取搜索到的 商品名称
        spname = self.driver.find_element(By.XPATH, '/html/body/div[5]/div[2]/div[2]/div[1]/ul/li/div[1]/em/a').text
        print('搜索到的商品名称:', spname)
        # 点击商品
        self.driver.find_element(By.XPATH, '/html/body/div[5]/div[2]/div[2]/div[1]/ul/li/div[1]/span/a/img').click()
        sleep(2)
        # 断言4 将搜索到的商品名称 和 商品详情页的商品名称对比
        dy4 = self.driver.find_element(By.XPATH, '//*[@id="goodsForm"]/div/div[1]/span').text
        print('断言4-商品名称(列表-详情名称对比):', dy4)
        self.assertEqual(spname, dy4)
        # 断言
        # 数据库断言 数据库根据id查找到商品的名称 和 商品详情页的商品名称对比 是否一致
        sql2 = f'select title from mall_goods where id=53'
        result2 = self.db.chaxun(sql2)
        print('数据库断言2查询结果：',result2[0][0])
        self.assertEqual(result2[0][0],dy4)
        sleep(2)
        # 查询商品ID=53的标题、它的分类关联时间、以及用户对它的评论
        # mall_goods g（商品表）和 mall_goods_category gc（分类关联表）和 mall_users_comment（商品评论表）
        # 用 g.id = gc.goods_id 关联，再和 mall_users_comment uc（评论表）用 g.id = uc.goods_id 关联
        sql3 = f'''
        select g.title, gc.create_time, uc.contents 
        from mall_goods g
        join mall_goods_category gc on g.id = gc.goods_id 
        join mall_users_comment uc on g.id = uc.goods_id where 
        g.id=53 and gc.goods_id=53 and uc.goods_id=53
        '''
        result3 = self.db.chaxun(sql3)
        print('数据库断言3查询结果：',result3)
        sleep(2)
        # 对商品数量进行清空
        self.driver.find_element(By.ID, 'store_nums').clear()
        sleep(2)
        # 数量框输入数量为2
        self.driver.find_element(By.ID, 'store_nums').send_keys('2')
        sleep(2)
        # 点击立即购买
        self.driver.find_element(By.ID, 'buy-cart').click()
        sleep(2)
        # 断言5 确认订单
        dy5 = self.driver.find_element(By.XPATH, '/html/body/div[4]/div[1]/a[2]').text
        print('断言5-确认订单：', dy5)
        self.assertTrue('确认订单', dy5)
        sleep(3)
        # 数据库断言 默认收货地址 和 数据库默认地址 对比
        dy6 = self.driver.find_element(By.XPATH,'//*[@id="theForm"]/div[1]/div[2]/div[1]/label').text
        print('断言6-收货地址：', dy6)
        # 2表联查
        # 数据库断言
        # 查询用户名和收货地址,条件是用户id为66且地址是默认
        # 表名：mall_users（用户表）、mall_users_address（用户地址表）
        # 关联条件是 用户表的 id = 地址表的 user_id，确保 “用户” 和 “地址” 对应
        sql4 = f'select u.username,ua.address from mall_users u join mall_users_address ua ' \
               f'on u.id = ua.user_id where ua.is_default=1 and user_id=66 '
        result4 = self.db.chaxun(sql4)
        print('2表联查(查询用户名和收货地址,条件是用户id为66且地址是默认)查询结果：', result4[0][0],result4[0][1])
        sleep(2)
        # 数据库查找到的地址和用户名 和 提取出来的地址进行对比 查看期望结果是否在实际结果里
        self.assertIn(result4[0][0],dy6)
        self.assertIn(result4[0][1],dy6)
        sleep(3)
        # 断言7 支付方式
        # dy7 = self.driver.find_element(By.XPATH, '/html/body/div[4]/form/div[3]/div[2]/div[1]/label/text()[1]').text
        # print('断言7：', dy7)
        # self.assertIn('余额支付（', dy7)
        # sleep(2)
        # 向下滚动
        self.driver.execute_script("window.scrollTo(0,500)")
        sleep(2)
        # 断言8 商品名称
        dy8 = self.driver.find_element(By.XPATH, '//*[@id="theForm"]/div[4]/div[1]/table/tbody/tr[2]/td[2]/a/p').text
        print('断言8：', dy8)
        self.assertEqual(dy4, dy8)
        sleep(2)
        # 提取商品需要支付的总金额（包含运费、数量、单价）
        order_amount = self.driver.find_element(By.ID, 'order-amount').text
        print('订单总价为：', order_amount)
        sleep(2)
        # 点击下一步
        self.driver.find_element(By.ID, 'order-button').click()
        sleep(2)
        # 断言10 您的订单支付成功
        dy10 = self.driver.find_element(By.CLASS_NAME, 'succe-ico').text
        print('断言10：', dy10)
        self.assertTrue('您的订单支付成功' == dy10)
        # 点击查看订单
        self.driver.find_element(By.XPATH, '/html/body/div[4]/div[3]/p/a').click()
        sleep(2)
        # 断言11-我的订单
        dy11 = self.driver.find_element(By.XPATH,
                                        '/html/body/div[5]/div[2]/div/div[1]/span').text
        print('断言11：', dy10)
        self.assertEqual('我的订单', dy11)
        sleep(2)
        # 点击申请退款
        self.driver.find_element(By.XPATH, '/html/body/div[5]/div[2]/div/div[2]/table[2]/tbody/tr[6]/td[2]/div[1]/a').click()
        sleep(2)
        # 断言12-退款详情
        dy12 = self.driver.find_element(By.XPATH,
                                        '/html/body/div[5]/div[2]/div/div[1]/span').text
        print('断言11：', dy10)
        self.assertEqual('退款详情', dy12)
        sleep(2)
        # 输入退款说明
        self.driver.find_element(By.NAME, 'message').send_keys('商品质量不好')
        sleep(2)
        # 点击提交
        self.driver.find_element(By.ID, 'user-info-btn').click()
        sleep(5)
        # 用登录余额 - 购买商品的总支付金额 = 用户此时余额
        gmh_yue = float(dy2s[1]) - float(order_amount)
        print('购买商品后计算的剩余余额：', gmh_yue)
        sleep(2)
        self.driver.refresh()
        # 用子查询判断用户 ID=66 是否有过订单（即是否买过商品）
        # 对指定用户，子查询统计其订单数量，>0 表示 “买过商品”
        # 表名：mall_users（用户表）、mall_order（订单表）、
        sql5 = f'select u.username,(select COUNT(*) from mall_order o where o.user_id = u.id) > 0' \
               f' from mall_users u where u.id=66'
        result5 = self.db.chaxun(sql5)
        print('数据库购买商品后的余额:', result5)
        print('子查询结果：',sql5)
        sleep(2)

        # A3mall-后台
        self.driver.get('http://10.59.9.14:7003/admin/login/index.html')
        self.driver.maximize_window()
        sleep(2)
        # 输入账号
        self.driver.find_element(By.NAME, 'username').send_keys('admin')
        sleep(2)
        # 输入密码
        self.driver.find_element(By.NAME, 'password').send_keys('admin888')
        sleep(10)
        # 点击登录
        self.driver.find_element(By.XPATH, '/html/body/div[2]/div/div/form/div[4]/button').click()
        sleep(5)
        # 点击查看 退款订单
        self.driver.find_element(By.XPATH, '/html/body/div/div/section/div/div[2]/div[2]/div[3]/div/div/div/a').click()
        sleep(7)
        # 点击详情
        # 点击退款
        self.driver.find_element(By.XPATH, '/html/body/div/div/section/div/form/div/div/div[1]/div[1]/div/a[3]').click()
        sleep(2)
        # 向下滑动
        self.driver.execute_script('window.scrollTo(0,300)')
        sleep(5)
        # 输入内容
        self.driver.find_element(By.NAME, 'desc').send_keys('同意退款该商品')
        sleep(2)
        # 点击提交
        self.driver.find_element(By.XPATH, '/html/body/div/div/section/div/form/div/div[2]/div/button[1]').click()
        sleep(2)


        # A3mall-前台
        self.driver.get('http://10.59.9.14:7003/')
        sleep(2)
        self.driver.maximize_window()
        sleep(2)
        # 点击会员中心
        dl = WebDriverWait(self.driver, 10, 0.5).until(
            EC.presence_of_element_located((By.XPATH, '/html/body/div[1]/div/div[2]/ul/li[1]/a')))
        dl.click()
        sleep(2)
        # 点击我的订单
        self.driver.find_element(By.XPATH, '/html/body/div[5]/div[1]/div[2]/div[1]/div[2]/ul/li[1]/a').click()
        sleep(2)
        # 提取后台退款的总金额
        tk_money = self.driver.find_element(By.XPATH, '/html/body/div[5]/div[2]/div[1]/div[2]/table/tbody/tr[3]/td[2]').text
        tk_moneyS = tk_money.split('￥')
        print('后台退款的总金额:', tk_moneyS[1])
        sleep(2)
        # 提取退款后的用户余额-数据库
        sql6 = f'select amount from mall_users where username={"18185603008"}'
        result6 = self.db.chaxun(sql6)
        print('退款后-数据库用户余额:', result6[0][0])
        self.assertEqual(float(dy2s[1]), float(result6[0][0]))
        sleep(2)
        # 用登录时的余额  退款的总余额 = 用户退款后的余额
        user_syye = float(gmh_yue) + float(tk_moneyS[1])
        print('退款后的用户余额应该为：', user_syye)
        sleep(2)
        # 计算的退款后余额 和 用户页面显示的余额 是否相等
        self.assertEqual(float(user_syye), float(result6[0][0]))
        # 表：mall_order（订单） + mall_order_goods（订单商品） + mall_users（用户）+ mall_goods（商品表）
        # 通过关联 4 张表，查询用户 ID=66购买“性能测试 - 水瓶” 商品的订单信息（用户名、订单号、商品标题 ）
        # 条件：指定用户购买的指定商品的信息
        sql7 = f'select u.username,o.order_no,g.title from mall_order o join mall_order_goods og ' \
               f'on o.id = og.order_id join mall_goods g on og.goods_id = g.id join mall_users u ' \
               f'on o.user_id = u.id where u.id=66 and title="性能测试-水瓶"'
        result7 = self.db.chaxun(sql7)
        print('4表联查-指定用户购买-指定商品的信息:', result7)
        # 关闭浏览器驱动
        sleep(3)
        self.driver.quit()



if __name__ == '__main__':
    unittest.main()
